{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11470\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "market = \"hair_dryer\"\n",
    "# market = \"microwave\"\n",
    "# market = \"pacifier\"\n",
    "\n",
    "inputexcel = pd.read_excel(\"../Problem_C_Data/\" + market + '.xlsx', market)\n",
    "# 评论的个数\n",
    "num_review = len(list(inputexcel['star_rating']))\n",
    "print(num_review)\n",
    "\n",
    "xlsxxlsx = '../Problem_C_Data/good_contain.xls'\n",
    "good_words = pd.read_excel(xlsxxlsx, 'Sheet1')\n",
    "good_words = good_words.key.values.tolist()\n",
    "xlsxxlsx = '../Problem_C_Data/bad_contain.xls'\n",
    "bad_words = pd.read_excel(xlsxxlsx, 'Sheet1')\n",
    "bad_words = bad_words.key.values.tolist()\n",
    "# print(bad_words[1:5])\n",
    "# good_words[3]\n",
    "\n",
    "\n",
    "writer = pd.ExcelWriter(\"./2e_output/apriori star rating descriptors 2e mlxtend select first contain \"+market+\".xlsx\",)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "code_folding": [
     1
    ]
   },
   "outputs": [],
   "source": [
    "import re\n",
    "def load_selected_star(inputexcel,star_rating_12345):\n",
    "    # 一行代表一个评论，一行第一个元素是star_rating\n",
    "    output = []\n",
    "    star_rating_list = list(inputexcel[inputexcel['star_rating']==star_rating_12345]['star_rating'])\n",
    "    review_headline_list = list(inputexcel[inputexcel['star_rating']==star_rating_12345]['review_headline'])\n",
    "    review_body_list = list(inputexcel[inputexcel['star_rating']==star_rating_12345]['review_body'])\n",
    "\n",
    "    for ii in range(len(star_rating_list)):\n",
    "    #     用正则表达式替换掉所有不是英文字母和空格的字符变成空格\n",
    "        review_headline = re.sub('[^a-zA-Z\\s]', ' ', str(review_headline_list[ii]))\n",
    "        review_body = re.sub('[^a-zA-Z\\s]', ' ', str(review_body_list[ii]))\n",
    "    #     避免review_headline最后一个单词和review_body第一个单词挨在一起，中间加个空格\n",
    "        review = review_headline + ' ' + review_body\n",
    "    #     小写 以空格分词\n",
    "        reviews = review.lower().split(' ')\n",
    "        onereview = []\n",
    "    #     提取出评论文本中的每个单词\n",
    "        for review_word in reviews:\n",
    "    #         如果在好词列表中，那么就加进该评论对应的的词表\n",
    "            if review_word in good_words:\n",
    "                onereview.append(review_word)\n",
    "    #         如果在坏词列表中，那么就加进该评论对应的的词表\n",
    "            if review_word in bad_words:\n",
    "                onereview.append(review_word)\n",
    "    #     将该评论的词表添加到所有评论词表末尾\n",
    "        output.append(list(str(star_rating_list[ii]))+onereview)\n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 选取出所有 star_rating_12345 的评论\n",
    "star_rating_12345 = 1\n",
    "df_arr = load_selected_star(inputexcel,star_rating_12345)\n",
    "\n",
    "# print(len(output))\n",
    "# for r in output[1:6]:\n",
    "#         print(r)\n",
    "# print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from mlxtend.preprocessing import TransactionEncoder\t\n",
    "# https://blog.csdn.net/qq_36523839/article/details/83960195https://blog.csdn.net/qq_36523839/article/details/83960195\n",
    "\n",
    "# 转换为bool值，也可以用函数转换为0、1\n",
    "\n",
    "te = TransactionEncoder()\t# 定义模型\n",
    "df_tf = te.fit_transform(df_arr)\n",
    "# 将 True、False 转换为 0、1 # 官方给的其它方法\n",
    "# df_01 = df_tf.astype('int')\t\t\t\n",
    "\n",
    "# 将编码值再次转化为原来的商品名\n",
    "# df_name = te.inverse_transform(df_tf)\t\t\n",
    "\n",
    "df = pd.DataFrame(df_tf,columns=te.columns_)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from mlxtend.frequent_patterns import apriori\n",
    "\n",
    "# use_colnames = True 使用元素名字\n",
    "# use_colnames = False 默认的 使用列名代表元素\n",
    "frequent_itemsets = apriori(df, min_support=0.05, use_colnames=True)\n",
    "\n",
    "# 频繁项集排序\n",
    "frequent_itemsets.sort_values(by='support', ascending=False, inplace=True)\n",
    "\n",
    "# 选择长度 >=2 的频繁项集\n",
    "# print(frequent_itemsets[frequent_itemsets.itemsets.apply(lambda x: len(x)) >= 2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   antecedents consequents  antecedent support  consequent support   support  \\\n",
      "38  (1, waste)     (money)            0.105620            0.160853  0.092054   \n",
      "39     (money)  (1, waste)            0.160853            0.105620  0.092054   \n",
      "34     (money)     (waste)            0.160853            0.105620  0.092054   \n",
      "40     (waste)  (money, 1)            0.105620            0.160853  0.092054   \n",
      "36  (money, 1)     (waste)            0.160853            0.105620  0.092054   \n",
      "\n",
      "    confidence      lift  leverage  conviction  \n",
      "38    0.871560  5.418371  0.075065    6.533361  \n",
      "39    0.572289  5.418371  0.075065    2.091085  \n",
      "34    0.572289  5.418371  0.075065    2.091085  \n",
      "40    0.871560  5.418371  0.075065    6.533361  \n",
      "36    0.572289  5.418371  0.075065    2.091085  \n",
      "\n",
      "    antecedents consequents  antecedent support  consequent support   support  \\\n",
      "0    (1, waste)     (money)            0.105620            0.160853  0.092054   \n",
      "1       (money)  (1, waste)            0.160853            0.105620  0.092054   \n",
      "2       (waste)  (money, 1)            0.105620            0.160853  0.092054   \n",
      "3    (money, 1)     (waste)            0.160853            0.105620  0.092054   \n",
      "4  (1, working)   (stopped)            0.147287            0.109496  0.087209   \n",
      "\n",
      "   confidence      lift  leverage  conviction  \n",
      "0    0.871560  5.418371  0.075065    6.533361  \n",
      "1    0.572289  5.418371  0.075065    2.091085  \n",
      "2    0.871560  5.418371  0.075065    6.533361  \n",
      "3    0.572289  5.418371  0.075065    2.091085  \n",
      "4    0.592105  5.407545  0.071082    2.183171  \n"
     ]
    }
   ],
   "source": [
    "from mlxtend.frequent_patterns import association_rules\n",
    "\n",
    "'''\n",
    "association_rules(df, metric=\"confidence\",\n",
    "                      min_threshold=0.8,\n",
    "                      support_only=False):\n",
    "\n",
    "- df：Apriori 计算后的频繁项集。\n",
    "\n",
    "- metric：\n",
    "可选值['support','confidence','lift','leverage','conviction']\n",
    "和下面的min_threshold参数配合使用\n",
    "\n",
    "- min_threshold：\n",
    "参数类型是浮点型，根据 metric 不同可选值有不同的范围，\n",
    "metric = 'support'  => 取值范围 [0,1]\n",
    "metric = 'confidence'  => 取值范围 [0,1]\n",
    "metric = 'lift'  => 取值范围 [0, inf]\n",
    "\n",
    "support_only：默认是 False。仅计算有支持度的项集，若缺失支持度则用 NaNs 填充。\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "association_rule = association_rules(\n",
    "    frequent_itemsets, metric='confidence', min_threshold=0.3)  # metric可以有很多的度量选项，返回的表列名都可以作为参数\n",
    "\n",
    "# 关联规则排序\n",
    "association_rule.sort_values(by='leverage', ascending=False, inplace=True)\n",
    "\n",
    "print(association_rule.head())\n",
    "\n",
    "print()\n",
    "\n",
    "association_rule = association_rule.values.tolist()\n",
    "\n",
    "\n",
    "# 查找所有 含有 star_rating_12345 的 rules_sorted\n",
    "rules_sorted_contain_star_rating_12345 = []\n",
    "for rules_sort in association_rule:\n",
    "    if (str(star_rating_12345) in rules_sort[0]) or (str(star_rating_12345) in rules_sort[1]):\n",
    "        rules_sorted_contain_star_rating_12345.append(rules_sort)\n",
    "\n",
    "column_name = ['antecedents', 'consequents', 'antecedent support',\n",
    "               'consequent support', 'support', 'confidence', 'lift', 'leverage', 'conviction']\n",
    "rules_sorted_contain_star_rating_12345 = pd.DataFrame(\n",
    "    rules_sorted_contain_star_rating_12345, columns=column_name)\n",
    "\n",
    "print(rules_sorted_contain_star_rating_12345.head())\n",
    "\n",
    "\n",
    "rules_sorted_contain_star_rating_12345.to_excel(\n",
    "    writer, sheet_name=str(star_rating_12345), index=False)\n",
    "\n",
    "\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# association_rule.loc[(association_rule[\"antecedents\"] == str(star_rating_12345)) or\n",
    "#                      (association_rule[\"consequents\"] == str(star_rating_12345))].head()\n",
    "# print(association_rule['antecedents'].str.contains(str(star_rating_12345)) |\n",
    "#       association_rule['consequents'].str.contains(str(star_rating_12345)))\n",
    "# booll = association_rule.loc[association_rule['antecedents'].str.contains(str(star_rating_12345)) |\n",
    "#                              association_rule['consequents'].str.contains(str(star_rating_12345))]\n",
    "\n",
    "# print(booll)\n",
    "# df[bool].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "# star_rating_12345 = 1\n",
    "# # rules_sort[0]\n",
    "# # association_rule[0:2]\n",
    "# for rules_sort in association_rule[0:2]:\n",
    "#     print((str(star_rating_12345) in rules_sort[0]) or (str(star_rating_12345) in rules_sort[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
